﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;


namespace 售后客户管理.Common
{
    public class SqlHelper
    {
        #region a new algorithm(算法) to instead of My old 

        /// <summary>
        /// get selection set to a datatable. 
        /// if sqlString do not use parameter, please sent Null to param
        /// </summary>
        /// <param name="sqlString">select script</param>
        /// <param name="connection">a sqlConnection</param>
        /// <param name="param">if sqlString do not use parameter, please sent Null to param</param>
        /// <returns>return a DataTable</returns>
        public static DataTable GetDataTable(string sqlString, SqlConnection connection, SqlParameter[] param)
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
            if (!sqlString.Contains("@"))
            {
                sqlString += "\n print @ok";
                param = new SqlParameter[] { new SqlParameter("@ok", "OK!") };
            }
            SqlCommand command = new SqlCommand(sqlString, connection);
            command.Parameters.AddRange(param);
            DataTable dt = new DataTable();
            try
            {
                connection.Open();
                SqlDataReader sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
                dt.Load(sdr);
                sdr.Close();
            }
            catch (Exception ex)
            {
                string info = "执行public static DataTable GetReader(string sqlString,SqlConnection connection,SqlParameter[]param)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                connection.Close();
                throw new Exception(info);
            }
            return dt;
        }
        /// <summary>
        /// use procedure to get selection to a datatable
        /// if sqlString do not use parameter, please sent Null to param
        /// </summary>
        /// <param name="sqlString">use procedure select script</param>
        /// <param name="connection">a sqlConnction</param>
        /// <param name="param">if sqlString do not use parameter, please sent Null to param</param>
        /// <returns>return a DataTable</returns>
        public static DataTable GetDataTable_Proc(string sqlString, SqlConnection connection, SqlParameter[] param)
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
            //if (!sqlString.Contains("@"))
            //{
            //    sqlString += "\n print @ok";
            //    param = new SqlParameter[] { new SqlParameter("@ok", "OK!") };
            //}
            SqlCommand command = new SqlCommand(sqlString, connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddRange(param);
            DataTable dt = new DataTable();
            try
            {
                connection.Open();
                SqlDataReader sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
                dt.Load(sdr);
                sdr.Close();
            }
            catch (Exception ex)
            {
                string info = "执行public static DataTable GetReader(string sqlString,SqlConnection connection,SqlParameter[]param)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            return dt;
        }
        /// <summary>
        /// get one result to object
        /// </summary>
        /// <param name="sqlString">select script</param>
        /// <param name="connection">a sqlConnection</param>
        /// <param name="param">if sqlString do not use parameter, please sent Null to param</param>
        /// <returns>return a object</returns>
        public static object SingleResult(string sqlString, SqlConnection connection,SqlParameter[] param)
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
            if (!sqlString.Contains("@"))
            {
                sqlString += "\n print @ok";
                param = new SqlParameter[] { new SqlParameter("@ok", "OK!") };
            }
            if (param==null)
            {
                sqlString += "\n print @ok";
                param = new SqlParameter[] { new SqlParameter("@ok", "OK!") };
            }
            SqlCommand sqlCommand = new SqlCommand(sqlString, connection);
            sqlCommand.Parameters.AddRange(param);
            try
            {
                connection.Open();
                return sqlCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string info = "执行public static object SingleResult(string sqlString)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            finally
            {
                connection.Close();
            }
        }

        public static int InsertUpdateDelete(string sqlString, SqlConnection connection, SqlParameter[] param)
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
            if (!sqlString.Contains("@"))
            {
                sqlString += "\n print @ok";
                param = new SqlParameter[] { new SqlParameter("@ok", "OK!") };
            }
            SqlCommand sqlCommand = new SqlCommand(sqlString, connection);
            sqlCommand.Parameters.AddRange(param);
            try
            {
                connection.Open();
                return sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string info = "执行public static int InsertUpdateDelete(string sqlString)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            finally
            {
                connection.Close();
            }
        }

        #endregion

        #region  不带参数SQL语句处理
        public static int SqlBulkCopyInsert(string tablename, DataTable dataTable, Hashtable ccl, string ConnectionString)
        {
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(ConnectionString);
            foreach (string str in ccl.Keys)
            {
                sqlBulkCopy.ColumnMappings.Add(str, ccl[str].ToString());
            }
            sqlBulkCopy.DestinationTableName = tablename;
            if (dataTable != null && dataTable.Rows.Count != 0)
            {
                sqlBulkCopy.WriteToServer(dataTable);
                sqlBulkCopy.Close();
                return dataTable.Rows.Count;
            }
            else
            {
                sqlBulkCopy.Close();
                return 0;
            }

        }

        public static int InsertUpdateDelete(string sqlString, string ConnectionString)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string info = "执行public static int InsertUpdateDelete(string sqlString)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();
            }
        }

        public static object SingleResult(string sqlString, string ConnectionString)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string info = "执行public static object SingleResult(string sqlString)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();
            }
        }

        public static DataTable GetReader(string sqlString, string ConnectionString)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (Exception ex)
            {
                string info = "执行public static object SingleResult(string sqlString)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            return dt;
        }

        #endregion

        #region  带参数SQL语句处理
        public static int InsertUpdateDelete(string sqlString, SqlParameter[] param, string ConnectionString)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            cmd.Parameters.AddRange(param);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string info = "执行public static int InsertUpdateDelete(string sqlString,SqlParameter [] param)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();
            }
        }

        public static int SingleUpdate(string sqlString, SqlParameter[] param, string ConnectionString)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlTransaction tran;
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            cmd.Parameters.AddRange(param);
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                int rowcount = cmd.ExecuteNonQuery();
                if (rowcount != 1)
                {

                    tran.Rollback();
                    return rowcount;
                }
                else
                {
                    tran.Commit();
                    return rowcount;
                }
            }
            catch (Exception ex)
            {
                string info = "执行public static int Update(string sqlString,SqlParameter [] param)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                //throw new Exception(info);
                return -2;
            }
            finally
            {
                conn.Close();

            }
        }

        public static object SingleResult(string sqlString, SqlParameter[] param, string ConnectionString)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            cmd.Parameters.AddRange(param);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string info = "执行public static object SingleResult(string sqlString,SqlParameter [] param)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();
            }
        }

        public static DataTable GetReader(string sqlString, SqlParameter[] param, string ConnectionString)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(sqlString, conn);
            cmd.Parameters.AddRange(param);
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (Exception ex)
            {
                string info = "执行 public static SqlDataReader GetReader(string sqlString,SqlParameter [] param)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            return dt;
        }

        #endregion

        #region  带参数SQL存储过程处理
        public static int InsertUpdateDelete_Proc(string SqlProc, SqlParameter[] param, string ConnectionString)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(SqlProc, conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string info = "执行public static int InsertUpdateDelete_Proc(string SqlProc, SqlParameter[] param)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();
            }
        }

        public static object SingleResult_Proc(string sqlProc, SqlParameter[] param, string ConnectionString)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(sqlProc, conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string info = "执行public static object SingleResult_Proc(string sqlProc, SqlParameter[] param)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();
            }
        }

        public static DataTable GetReader_Proc(string sqlProc, SqlParameter[] param, string ConnectionString)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(sqlProc, conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (Exception ex)
            {
                string info = "执行public static SqlDataReader GetReader_Proc(string sqlProc, SqlParameter[] param)错误：" + ex.Message;
                WriteText.WriteLog(ex);
                throw new Exception(info);
            }
            return dt;
        }

        #endregion


    }
}
